const express = require('express');
const mysql = require('mysql2/promise');
const path = require('path');
const fs = require('fs');
const multer = require('multer');
const session = require('express-session');
//const bcrypt = require('bcrypt');
const bcrypt = require('bcryptjs');
require('dotenv').config();

const app = express();
const PORT = process.env.APP_PORT || 7531;
const HOST = process.env.APP_HOST || '0.0.0.0';

// 中间件
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

// 会话配置
app.use(session({
    secret: process.env.SESSION_SECRET || 'your-secret-key-change-in-production',
    resave: false,
    saveUninitialized: false,
    cookie: {
        secure: false, // 在生产环境中设置为true（需要HTTPS）
        maxAge: 24 * 60 * 60 * 1000 // 24小时
    }
}));

// 配置multer用于文件上传
const storage = multer.diskStorage({
    destination: function (req, file, cb) {
        const uploadDir = path.join(__dirname, 'uploads');
        if (!fs.existsSync(uploadDir)) {
            fs.mkdirSync(uploadDir, { recursive: true });
        }
        cb(null, uploadDir);
    },
    filename: function (req, file, cb) {
        const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
        cb(null, 'background-' + uniqueSuffix + path.extname(file.originalname));
    }
});

const upload = multer({
    storage: storage,
    limits: {
        fileSize: 5 * 1024 * 1024 // 5MB限制
    },
    fileFilter: function (req, file, cb) {
        if (file.mimetype.startsWith('image/')) {
            cb(null, true);
        } else {
            cb(new Error('只允许上传图片文件'));
        }
    }
});

// 静态文件服务
app.use(express.static('.', {
    index: false,
    setHeaders: (res, path) => {
        if (path.endsWith('.html')) {
            res.setHeader('Content-Type', 'text/html; charset=utf-8');
        }
    }
}));

// 上传文件静态服务
app.use('/uploads', express.static(path.join(__dirname, 'uploads')));

// 数据库连接配置
const dbConfig = {
    host: process.env.DB_HOST || '127.0.0.1',
    port: process.env.DB_PORT || 3306,
    user: process.env.DB_USER || 'myadmin',
    password: process.env.DB_PASSWORD || 'LSAx@2843493',
    database: process.env.DB_NAME || 'navigation_db',
    charset: 'utf8mb4',
    timezone: '+08:00'
};

let db;

// 初始化数据库连接
async function initDatabase() {
    try {
        // 首先连接到MySQL服务器（不指定数据库）
        const connection = await mysql.createConnection({
            host: dbConfig.host,
            port: dbConfig.port,
            user: dbConfig.user,
            password: dbConfig.password,
            charset: dbConfig.charset,
            timezone: dbConfig.timezone
        });

        // 创建数据库（如果不存在）
        await connection.execute(`CREATE DATABASE IF NOT EXISTS \`${dbConfig.database}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
        await connection.end();

        // 连接到指定数据库
        db = await mysql.createConnection(dbConfig);

        // 创建用户表
        await db.execute(`
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
                password VARCHAR(255) NOT NULL COMMENT '密码（加密）',
                role ENUM('admin', 'user') DEFAULT 'user' COMMENT '用户角色',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                INDEX idx_username (username)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表'
        `);

        // 创建网站设置表
        await db.execute(`
            CREATE TABLE IF NOT EXISTS site_settings (
                id INT AUTO_INCREMENT PRIMARY KEY,
                setting_key VARCHAR(100) NOT NULL UNIQUE COMMENT '设置键',
                setting_value TEXT COMMENT '设置值',
                description VARCHAR(255) COMMENT '设置描述',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                INDEX idx_setting_key (setting_key)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='网站设置表'
        `);

        // 插入默认设置
        await db.execute(
            'INSERT IGNORE INTO site_settings (setting_key, setting_value, description) VALUES (?, ?, ?), (?, ?, ?)',
            ['site_title', '我的导航网站', '网站标题', 'site_subtitle', '收集和分享优质网站资源', '网站副标题']
        );

        // 创建导航表
        await db.execute(`
            CREATE TABLE IF NOT EXISTS navigation (
                id INT AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(255) NOT NULL COMMENT '网站标题',
                url TEXT NOT NULL COMMENT '网站链接',
                description TEXT COMMENT '网站描述',
                status ENUM('enabled', 'disabled') DEFAULT 'enabled' COMMENT '状态：enabled启用，disabled禁用',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                INDEX idx_title (title),
                INDEX idx_created_at (created_at),
                INDEX idx_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='导航网站表'
        `);

        // 为已存在的表添加status字段（如果不存在）
        try {
            await db.execute(`
                ALTER TABLE navigation 
                ADD COLUMN status ENUM('enabled', 'disabled') DEFAULT 'enabled' COMMENT '状态：enabled启用，disabled禁用'
            `);
            console.log('已为navigation表添加status字段');
        } catch (error) {
            // 字段已存在，忽略错误
            if (!error.message.includes('Duplicate column name')) {
                console.error('添加status字段失败:', error);
            }
        }

        // 为status字段添加索引（如果不存在）
        try {
            await db.execute(`ALTER TABLE navigation ADD INDEX idx_status (status)`);
        } catch (error) {
            // 索引已存在，忽略错误
            if (!error.message.includes('Duplicate key name')) {
                console.error('添加status索引失败:', error);
            }
        }

        // 初始化超级管理员用户
        const [userRows] = await db.execute('SELECT COUNT(*) as count FROM users');
        if (userRows[0].count === 0) {
            const hashedPassword = await bcrypt.hash('admin123', 10);
            await db.execute(
                'INSERT INTO users (username, password, role) VALUES (?, ?, ?)',
                ['admin', hashedPassword, 'admin']
            );
            console.log('已创建超级管理员用户: admin/admin123');
        }

        // 插入示例数据（如果表为空）
        const [rows] = await db.execute('SELECT COUNT(*) as count FROM navigation');
        if (rows[0].count === 0) {
            const sampleData = [
                {
                    title: 'Google',
                    url: 'https://www.google.com',
                    description: '全球最大的搜索引擎'
                },
                {
                    title: 'GitHub',
                    url: 'https://github.com',
                    description: '全球最大的代码托管平台'
                },
                {
                    title: 'Stack Overflow',
                    url: 'https://stackoverflow.com',
                    description: '程序员问答社区'
                },
                {
                    title: 'MDN Web Docs',
                    url: 'https://developer.mozilla.org',
                    description: 'Web开发技术文档'
                },
                {
                    title: 'Vue.js',
                    url: 'https://vuejs.org',
                    description: '渐进式JavaScript框架'
                },
                {
                    title: 'Node.js',
                    url: 'https://nodejs.org',
                    description: 'JavaScript运行时环境'
                }
            ];

            for (const item of sampleData) {
                await db.execute(
                    'INSERT INTO navigation (title, url, description) VALUES (?, ?, ?)',
                    [item.title, item.url, item.description]
                );
            }
            console.log('已插入示例数据');
        }

        console.log('数据库初始化成功');
    } catch (error) {
        console.error('数据库初始化失败:', error);
        process.exit(1);
    }
}

// 认证中间件
function requireAuth(req, res, next) {
    if (req.session && req.session.userId) {
        return next();
    } else {
        return res.status(401).json({ error: '请先登录' });
    }
}

// 管理员权限中间件
function requireAdmin(req, res, next) {
    if (req.session && req.session.userId && req.session.userRole === 'admin') {
        return next();
    } else {
        return res.status(403).json({ error: '需要管理员权限' });
    }
}

// API路由

// 用户登录
app.post('/api/login', async (req, res) => {
    try {
        const { username, password } = req.body;
        
        if (!username || !password) {
            return res.status(400).json({ error: '用户名和密码不能为空' });
        }
        
        const [users] = await db.execute(
            'SELECT id, username, password, role FROM users WHERE username = ?',
            [username]
        );
        
        if (users.length === 0) {
            return res.status(401).json({ error: '用户名或密码错误' });
        }
        
        const user = users[0];
        const isValidPassword = await bcrypt.compare(password, user.password);
        
        if (!isValidPassword) {
            return res.status(401).json({ error: '用户名或密码错误' });
        }
        
        // 设置会话
        req.session.userId = user.id;
        req.session.username = user.username;
        req.session.userRole = user.role;
        
        res.json({
            message: '登录成功',
            user: {
                id: user.id,
                username: user.username,
                role: user.role
            }
        });
    } catch (error) {
        console.error('登录失败:', error);
        res.status(500).json({ error: '登录失败' });
    }
});

// 用户登出
app.post('/api/logout', (req, res) => {
    req.session.destroy((err) => {
        if (err) {
            return res.status(500).json({ error: '登出失败' });
        }
        res.json({ message: '登出成功' });
    });
});

// 检查登录状态
app.get('/api/auth/status', (req, res) => {
    if (req.session && req.session.userId) {
        res.json({
            isLoggedIn: true,
            user: {
                id: req.session.userId,
                username: req.session.username,
                role: req.session.userRole
            }
        });
    } else {
        res.json({ isLoggedIn: false });
    }
});

// 上传背景图片
app.post('/api/upload/background', requireAdmin, upload.single('background'), async (req, res) => {
    try {
        if (!req.file) {
            return res.status(400).json({ error: '请选择要上传的图片' });
        }
        
        const filename = req.file.filename;
        const filePath = `/uploads/${filename}`;
        
        // 保存到数据库设置表
        await db.execute(
            'INSERT INTO site_settings (setting_key, setting_value, description) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), updated_at = CURRENT_TIMESTAMP',
            ['background_image', filePath, '网站背景图片']
        );
        
        res.json({
            message: '背景图片上传成功',
            filePath: filePath
        });
    } catch (error) {
        console.error('上传背景图片失败:', error);
        res.status(500).json({ error: '上传失败' });
    }
});

// 获取网站设置
app.get('/api/settings', async (req, res) => {
    try {
        const [rows] = await db.execute(
            'SELECT setting_key, setting_value FROM site_settings'
        );
        
        const settings = {};
        rows.forEach(row => {
            settings[row.setting_key] = row.setting_value;
        });
        
        // 设置默认值
        if (!settings.site_title) {
            settings.site_title = '我的导航网站';
        }
        if (!settings.site_subtitle) {
            settings.site_subtitle = '收集和分享优质网站资源';
        }
        
        res.json(settings);
    } catch (error) {
        console.error('获取网站设置失败:', error);
        res.status(500).json({ error: '获取设置失败' });
    }
});

// 更新网站设置
app.put('/api/settings', requireAdmin, async (req, res) => {
    try {
        const { settings } = req.body;
        
        if (!settings || typeof settings !== 'object') {
            return res.status(400).json({ error: '设置数据格式错误' });
        }
        
        // 设置默认值
        const finalSettings = {
            site_title: settings.site_title || '我的导航网站',
            site_subtitle: settings.site_subtitle || '收集和分享优质网站资源',
            ...settings
        };
        
        for (const [key, value] of Object.entries(finalSettings)) {
            await db.execute(
                'INSERT INTO site_settings (setting_key, setting_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), updated_at = CURRENT_TIMESTAMP',
                [key, value]
            );
        }
        
        res.json({ success: true, message: '设置更新成功' });
    } catch (error) {
        console.error('更新网站设置失败:', error);
        res.status(500).json({ error: '更新设置失败' });
    }
});

// 获取所有导航数据
app.get('/api/navigation', async (req, res) => {
    try {
        const [rows] = await db.execute(
            'SELECT id, title, url, description, status, created_at, updated_at FROM navigation ORDER BY created_at DESC'
        );
        res.json(rows);
    } catch (error) {
        console.error('获取导航数据失败:', error);
        res.status(500).json({ error: '获取数据失败' });
    }
});

// 获取单个导航数据
app.get('/api/navigation/:id', async (req, res) => {
    try {
        const { id } = req.params;
        const [rows] = await db.execute(
            'SELECT id, title, url, description, status, created_at, updated_at FROM navigation WHERE id = ?',
            [id]
        );
        
        if (rows.length === 0) {
            return res.status(404).json({ error: '数据不存在' });
        }
        
        res.json(rows[0]);
    } catch (error) {
        console.error('获取导航数据失败:', error);
        res.status(500).json({ error: '获取数据失败' });
    }
});

// 添加新的导航数据
app.post('/api/navigation', requireAdmin, async (req, res) => {
    try {
        const { title, url, description, status = 'enabled' } = req.body;
        
        if (!title || !url) {
            return res.status(400).json({ error: '标题和链接不能为空' });
        }
        
        // 验证URL格式
        try {
            new URL(url);
        } catch {
            return res.status(400).json({ error: '请输入有效的URL' });
        }
        
        // 验证status值
        if (status && !['enabled', 'disabled'].includes(status)) {
            return res.status(400).json({ error: '状态值无效' });
        }
        
        const [result] = await db.execute(
            'INSERT INTO navigation (title, url, description, status) VALUES (?, ?, ?, ?)',
            [title.trim(), url.trim(), description ? description.trim() : null, status]
        );
        
        res.status(201).json({
            id: result.insertId,
            title: title.trim(),
            url: url.trim(),
            description: description ? description.trim() : null,
            status: status,
            message: '添加成功'
        });
    } catch (error) {
        console.error('添加导航数据失败:', error);
        res.status(500).json({ error: '添加数据失败' });
    }
});

// 更新导航数据
app.put('/api/navigation/:id', requireAdmin, async (req, res) => {
    try {
        const { id } = req.params;
        const { title, url, description, status } = req.body;
        
        if (!title || !url) {
            return res.status(400).json({ error: '标题和链接不能为空' });
        }
        
        // 验证URL格式
        try {
            new URL(url);
        } catch {
            return res.status(400).json({ error: '请输入有效的URL' });
        }
        
        // 验证status值
        if (status && !['enabled', 'disabled'].includes(status)) {
            return res.status(400).json({ error: '状态值无效' });
        }
        
        const [result] = await db.execute(
            'UPDATE navigation SET title = ?, url = ?, description = ?, status = ? WHERE id = ?',
            [title.trim(), url.trim(), description ? description.trim() : null, status || 'enabled', id]
        );
        
        if (result.affectedRows === 0) {
            return res.status(404).json({ error: '数据不存在' });
        }
        
        res.json({ message: '更新成功' });
    } catch (error) {
        console.error('更新导航数据失败:', error);
        res.status(500).json({ error: '更新数据失败' });
    }
});

// 切换导航状态
app.patch('/api/navigation/:id/status', requireAdmin, async (req, res) => {
    try {
        const { id } = req.params;
        const { status } = req.body;
        
        // 验证status值
        if (!status || !['enabled', 'disabled'].includes(status)) {
            return res.status(400).json({ error: '状态值无效，必须是enabled或disabled' });
        }
        
        const [result] = await db.execute(
            'UPDATE navigation SET status = ? WHERE id = ?',
            [status, id]
        );
        
        if (result.affectedRows === 0) {
            return res.status(404).json({ error: '数据不存在' });
        }
        
        res.json({ 
            message: `已${status === 'enabled' ? '启用' : '禁用'}网站`,
            status: status
        });
    } catch (error) {
        console.error('切换导航状态失败:', error);
        res.status(500).json({ error: '操作失败' });
    }
});

// 删除导航数据
app.delete('/api/navigation/:id', requireAdmin, async (req, res) => {
    try {
        const { id } = req.params;
        
        const [result] = await db.execute('DELETE FROM navigation WHERE id = ?', [id]);
        
        if (result.affectedRows === 0) {
            return res.status(404).json({ error: '数据不存在' });
        }
        
        res.json({ message: '删除成功' });
    } catch (error) {
        console.error('删除导航数据失败:', error);
        res.status(500).json({ error: '删除数据失败' });
    }
});

// 路由处理
app.get('/', (req, res) => {
    res.sendFile(path.join(__dirname, 'index.html'));
});

app.get('/admin', (req, res) => {
    res.sendFile(path.join(__dirname, 'admin.html'));
});

app.get('/login', (req, res) => {
    res.sendFile(path.join(__dirname, 'login.html'));
});

// 404处理
app.use((req, res) => {
    res.status(404).json({ error: '页面不存在' });
});

// 错误处理中间件
app.use((error, req, res, next) => {
    console.error('服务器错误:', error);
    res.status(500).json({ error: '服务器内部错误' });
});

// 优雅关闭
process.on('SIGINT', async () => {
    console.log('\n正在关闭服务器...');
    if (db) {
        await db.end();
        console.log('数据库连接已关闭');
    }
    process.exit(0);
});

process.on('SIGTERM', async () => {
    console.log('收到SIGTERM信号，正在关闭服务器...');
    if (db) {
        await db.end();
        console.log('数据库连接已关闭');
    }
    process.exit(0);
});

// 启动服务器
async function startServer() {
    try {
        await initDatabase();
        
        app.listen(PORT, HOST, () => {
            console.log(`\n🚀 导航网站服务器已启动`);
            console.log(`📱 前端地址: http://localhost:${PORT}`);
            console.log(`⚙️  管理后台: http://localhost:${PORT}/admin`);
            console.log(`🔗 API地址: http://localhost:${PORT}/api/navigation`);
            console.log(`\n按 Ctrl+C 停止服务器\n`);
        });
    } catch (error) {
        console.error('启动服务器失败:', error);
        process.exit(1);
    }
}

startServer();